package net.huashitong.Indicators.dao;

import java.text.ParseException;
import java.util.List;
import java.util.Map;

import net.huashitong.Indicators.entity.IndicatorBreakdown;
import net.huashitong.Indicators.filter.IndicatorBreakdownFilter;
import net.huashitong.Indicators.utils.DateForIndUtils;

import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.core.filter.BaseFilter;
import com.sirdc.modules.utils.StringUtils;
@Repository
public class IndicatorBreakdownDao extends StringDao<IndicatorBreakdown> {

	public List<IndicatorBreakdown> queryByCuMonthAndArea(
			String currentMonth, String area) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth",currentMonth);
		query.append(" and obj.area = :area").setParam("area", area);
		return query.listResult();
	}
	
	public List<IndicatorBreakdown> queryByCuMonthAndArea(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth",filter.getCurrentMonth());
		query.append(" and obj.industryId = :industryId order by cast(obj.cumulative as int) desc").setParam("industryId", filter.getIndustryId());
		return query.listResult();
	}

	public List<IndicatorBreakdown> queryDevelopmentSituation(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		String total = dealTime(filter.getCurrentMonth());
		if("03".contains(filter.getCurrentMonth())){
			query.append(" and obj.date >= :dateS").setParam("dateS", filter.getCurrentMonth().substring(0,4));
		}else {
			String lastTotal = lastDealTime(filter.getCurrentMonth());
			query.append(" and obj.date >= :dateS").setParam("dateS", lastTotal);
		}
		query.append(" and obj.date <= :date").setParam("date", total);
		query.append(" and obj.area = :area").setParam("area", filter.getArea());
		query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		return query.listResult();
	}
	
	public List<IndicatorBreakdown> queryMonthDevelopmentSituation(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		if("03".contains(filter.getCurrentMonth())){
			query.append(" and obj.currentMonth >= :currentMonthS").setParam("currentMonthS", filter.getCurrentMonth().substring(0,4));
		}else {
			String lastTotal = lastMonthTime(filter.getCurrentMonth());
			query.append(" and obj.currentMonth >= :currentMonthS").setParam("currentMonthS", lastTotal);
		}
			query.append(" and obj.currentMonth <= :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
		query.append(" and obj.area = :area").setParam("area", filter.getArea());
		query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		return query.listResult();
	}
	public String dealTime(String currentMonth){
		String year = currentMonth.substring(0,4);
		String month = currentMonth.substring(4);
		String total = year+"01-"+month;
		return total;
	}
	public static String lastDealTime(String currentMonth){
		String year = currentMonth.substring(0,4);
		String month = currentMonth.substring(4);
		int iyear = Integer.parseInt(month);
		iyear = iyear - 3;
		String total = "";
		if(iyear>9){
			total = year+"01-"+iyear;
		}else{
			total = year+"01-0"+iyear;
		}
		
		return total;
	}
	public static String lastMonthTime(String currentMonth){
		String year = currentMonth.substring(0,4);
		String month = currentMonth.substring(4);
		int iyear = Integer.parseInt(month);
		iyear = iyear - 1;
		String total = "";
		if(iyear>9){
			total = year+iyear;
		}else{
			total = year+"0"+iyear;
		}
		
		return total;
	}
	public List<IndicatorBreakdown> queryAbsoluteValue(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.date like :date").setParam("date", StringUtils.getLikewords(filter.getDate()));
		query.append(" and obj.date <= :dateSec").setParam("dateSec", DateForIndUtils.getMonth(filter.getCurrentMonth()));
		query.append(" and obj.area = :area").setParam("area", filter.getArea());
		query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		query.getOrders().put("date", BaseFilter.DESC);	
		return query.listResult();
	}
	public List<IndicatorBreakdown> queryMonthAbsolute(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.currentMonth like :currentMonth").setParam("currentMonth", StringUtils.getLikewords(filter.getDate()));
		try {
			query.append(" and obj.currentMonth < :month").setParam("month", DateForIndUtils.getNextMonthDate(filter.getCurrentMonth(), 1));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		query.append(" and obj.area = :area").setParam("area", filter.getArea());
		query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		query.getOrders().put("currentMonth", BaseFilter.DESC);
		return query.listResult();
	}
	public List<Map<String,Object>> queryAll(String date, String industryName, String area) {
		String hql = "select a.industry_id as industry_id,a.ratio,a.industry_name,a.cumulative,"
				+ "a.rank, "
				+ " a.county_increase,a.unit "
				+ "FROM t_gis_indicator_breakdown AS a  where a.date=?"
				+ " and a.industry_name=? and a.area = ?  ORDER BY a.ratio";
		SQLQuery query = getSession().createSQLQuery(hql);
		query.setParameter(0, date);
		query.setParameter(1, industryName);
	    query.setParameter(2, area);
		
		List list = query.list();
		return list;
	}

	public List<IndicatorBreakdown> queryShopping(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.area in (:areas)").setParamList("areas", filter.getAreas());
		query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		try {
			query.append(" and (obj.date like :dateSe or obj.date like :dateTh)")
			.setParam("dateSe", StringUtils.getLikewords(DateForIndUtils.getNextYearDate(filter.getDate().substring(0,4), -1)))
			.setParam("dateTh", StringUtils.getLikewords(DateForIndUtils.getNextYearDate(filter.getDate().substring(0,4), -2)));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		query.append(" and obj.date not like '%%-03' ORDER BY obj.date DESC");
		return query.listResult();
	}

	public List<IndicatorBreakdown> queryShoppingInd(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.area in (:areas)").setParamList("areas", filter.getAreas());
		query.append(" and obj.industryName in(:industryNames)").setParamList("industryNames", filter.getIndustryNames());
		query.append(" and obj.date = :date ORDER BY obj.area").setParam("date", filter.getDate());
		return query.listResult();
	}

	public List<String> queryCountyRankArea(IndicatorBreakdownFilter filter) {
		String hql = "select area from IndicatorBreakdown obj";
		GenericQuery query = create(hql);
		query.append(" where obj.currentMonth = :currentMonth").setParam("currentMonth",filter.getCurrentMonth());
		query.append(" and obj.industryId = :industryId order by cast(sysId as int) desc").setParam("industryId", filter.getIndustryId());
		return query.listResult();
	}

	public IndicatorBreakdown queryMonthValue(IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth",filter.getCurrentMonth());
		if(StringUtils.isNotBlank(filter.getIndustryId())){
			query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		}
		query.append(" and obj.area = :area").setParam("area", filter.getArea());
		return query.singleResult();
	}

	public List<IndicatorBreakdown> queryMonthValueSec(
			IndicatorBreakdownFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth",filter.getCurrentMonth());
		if(StringUtils.isNotBlank(filter.getIndustryId())){
			query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
		}
		query.append(" and obj.area = :area").setParam("area", filter.getArea());
		return query.listResult();
	}

	public IndicatorBreakdown queryByDateAndId(
			IndicatorBreakdown indicatorBreakdown) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.date = :date").setParam("date",indicatorBreakdown.getDate());
		query.append(" and obj.industryId = :industryId").setParam("industryId", indicatorBreakdown.getIndustryId());
		query.append(" and obj.area = :area").setParam("area", indicatorBreakdown.getArea());
		return query.singleResult();
	}

	public List queryAllSec(String date, String industryName, String area) {
		String hql = "select c.sys_id as industry_id,a.ratio,a.industry_name,a.cumulative,"
				+ "(select COUNT(*) from t_gis_indicator_breakdown b where cast(b.cumulative as DECIMAL(10,4)) >= cast(a.cumulative as DECIMAL(10,4)) and b.date= ? and b.industry_name= ?) rank, "
				+ " c.county_increase,c.unit "
				+ "FROM t_gis_indicator_breakdown AS a left join t_gis_tertiary_industry c on c.indicator_name = a.industry_name where a.date=?"
				+ " and a.industry_name=? and a.area = ?";
		SQLQuery query = getSession().createSQLQuery(hql);
		query.setParameter(0, date);
		query.setParameter(1, industryName);
		query.setParameter(2, date);
		query.setParameter(3, industryName);
		query.setParameter(4, area);
		
		List list = query.list();
		return list;
	}
	
}
